package pers.zxk.jlytnews.dao.impl;

import pers.zxk.jlytnews.dao.FoodDao;
import pers.zxk.jlytnews.entity.Food;
import pers.zxk.jlytnews.entity.FoodWithType;
import pers.zxk.jlytnews.util.DBconn;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class FoodDaoImpl implements FoodDao {
    @Override
    public boolean addFood(String name, int type, String image, String description, String remarks) throws SQLException, ClassNotFoundException {
        boolean flag = false;
        DBconn.init();
        int i = DBconn.addUpdDel("insert into tb_food(name,type,image,description,remarks) " +
                "values('" + name + "','" + type + "','" + image + "','" + description + "','" + remarks + "')");
        if (i > 0) {
            flag = true;
        }
        DBconn.closeConn();
        return flag;
    }

    @Override
    public boolean delFood(int id) throws SQLException, ClassNotFoundException {
        boolean flag = false;
        DBconn.init();
        String sql = "delete from tb_food where id=" + id;
        int i = DBconn.addUpdDel(sql);
        if (i > 0) {
            flag = true;
        }
        DBconn.closeConn();
        return flag;
    }

    @Override
    public boolean updateFood(int id, String name, int type, String image, String description, String remarks) throws SQLException, ClassNotFoundException {
        boolean flag = false;
        DBconn.init();
        String sql = "update tb_food set name ='" + name
                + "' , type ='" + type
                + "' , image ='" + image
                + "' , description ='" + description
                + "' , remarks ='" + remarks + "' where id = " + id;
        int i = DBconn.addUpdDel(sql);
        if (i > 0) {
            flag = true;
        }
        DBconn.closeConn();
        return flag;
    }

    @Override
    public Food getFood(int id) throws SQLException, ClassNotFoundException {

        DBconn.init();
        ResultSet rs = DBconn.selectSql("select * from tb_food where id = " + id);
        if (rs.next()) {
            Food food = new Food(rs.getInt("id"),
                    rs.getString("name"),
                    rs.getInt("type"),
                    rs.getString("image"),
                    rs.getString("description"),
                    rs.getString("remarks"));
            return food;
        } else {
            DBconn.closeConn();
            throw new SQLException("未找到id为" + id + "的数据");
        }

    }

    @Override
    public List<Food> getFoods(int type) throws SQLException, ClassNotFoundException {
        List<Food> list = new ArrayList<Food>();
        DBconn.init();
        ResultSet rs = DBconn.selectSql("select * from tb_food where type = " + type + " order by id DESC ");
        while (rs.next()) {
            Food food = new Food();
            food.setId(rs.getInt("id"));
            food.setName(rs.getString("name"));
            food.setType(rs.getInt("type"));
            food.setImage(rs.getString("image"));
            food.setDescription(rs.getString("description"));
            food.setRemarks(rs.getString("remarks"));
            list.add(food);
        }
        DBconn.closeConn();
        return list;
    }

    @Override
    public List<Food> getAllFood() throws SQLException, ClassNotFoundException {
        List<Food> list = new ArrayList<Food>();
        DBconn.init();
        ResultSet rs = DBconn.selectSql("select * from tb_food order by id DESC ");
        while (rs.next()) {
            Food food = new Food();
            food.setId(rs.getInt("id"));
            food.setName(rs.getString("name"));
            food.setType(rs.getInt("type"));
            food.setImage(rs.getString("image"));
            food.setDescription(rs.getString("description"));
            food.setRemarks(rs.getString("remarks"));
            list.add(food);
        }
        DBconn.closeConn();
        return list;

    }

    @Override
    public List<FoodWithType> getAllFoodWithType() throws SQLException, ClassNotFoundException {
        List<FoodWithType> list = new ArrayList<FoodWithType>();
        DBconn.init();
        ResultSet rs = DBconn.selectSql("select * from tb_food left join tb_foodtype on tb_food.type = tb_foodtype.id order by tb_food.id DESC ");
        while (rs.next()) {
            FoodWithType food = new FoodWithType();
            food.setId(rs.getInt("id"));
            food.setName(rs.getString("name"));
            food.setType(rs.getInt("type"));
            food.setImage(rs.getString("image"));
            food.setDescription(rs.getString("description"));
            food.setRemarks(rs.getString("remarks"));
            food.setTypeName(rs.getString("tb_foodtype.name"));
            food.setTypeRemarks(rs.getString("tb_foodtype.remarks"));
            list.add(food);
        }
        DBconn.closeConn();
        return list;
    }
}
